package com.lbb.springboot.auto.util;
  
import java.sql.Connection;  
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;  
import java.sql.Statement;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

  
public class MysqlHelper {  
    public static final String url = "jdbc:mysql://43.136.115.228:3306/xcx?useUnicode=true&characterEncoding=UTF-8";
    public static final String name = "com.mysql.jdbc.Driver";  
    public static final String user = "root";  
    public static final String password = "tKsMMdLYWWJKWrHX";
  
    public Connection conn = null;  
  //  public PreparedStatement pst = null;  
  
    public MysqlHelper() {  
        try {  
            Class.forName(name);//指定连接类型  
            conn = DriverManager.getConnection(url, user, password);//获取连接  
          //  pst = conn.prepareStatement(sql);//准备执行语句  
        } catch (Exception e) {  
            e.printStackTrace();  
        }  
    }  
  
    public void close() {  
        try {  
            this.conn.close();  
        } catch (SQLException e) {  
            e.printStackTrace();  
        }  
    }  
    
    public ResultSet query(String sql) throws SQLException{
    	Statement st=conn.createStatement();
    	 return st.executeQuery(sql);
    }
    
    public static void main(String args[]){	
    	new MysqlHelper();
    }
    
    
    
    
  	/**
  	 * 组装插入语句
  	 * @param tableName
  	 * @param map
  	 * @return
  	 */
  	public static  String getInsertSQL(String tableName,HashMap<String,Object> map){
  		StringBuffer sb1=null;
  		StringBuffer sb2=null;
  		Iterator it2=map.entrySet().iterator();
  		while(it2.hasNext()){
  			Map.Entry entry2=(Map.Entry)it2.next();
  			Object key2 = entry2.getKey();
  			Object val2 = entry2.getValue();
  			if(sb1==null){
  				sb1=new StringBuffer();
  				sb2=new StringBuffer();
  				sb1.append(key2);
  				sb2.append("'").append(val2).append("'");
  			}else{
  		         sb1.append(","+key2);
  				 sb2.append(",'"+val2).append("'");
  			}
  		
  		} 
         return String.format("insert into %s (%s) values(%s)", new Object[]{tableName,sb1.toString(),sb2.toString()});
  	}
  	
  	
  	
  	public static String getInsertSQL(String tableName,HashMap<String,Object> pk,HashMap<String,Object> col){
  		StringBuffer sb1=null;
  		StringBuffer sb2=null;
  		Iterator it1=pk.entrySet().iterator();
  		while(it1.hasNext()){
  			Map.Entry entry1=(Map.Entry)it1.next();
  			Object key1 = entry1.getKey();
  			Object val1 = entry1.getValue();
  			if(sb1==null){
  				sb1=new StringBuffer();
  				sb2=new StringBuffer();
  				sb1.append(key1);
  				sb2.append("'").append(val1).append("'");
  			}else{
  		         sb1.append(","+key1);
  				 sb2.append(",'"+val1).append("'");
  			}
  		    
  		}
  		
  		Iterator it2=col.entrySet().iterator();
  		while(it2.hasNext()){
  			Map.Entry entry2=(Map.Entry)it2.next();
  			Object key2 = entry2.getKey();
  			Object val2 = entry2.getValue();
  			if(sb1==null){
  				sb1=new StringBuffer();
  				sb2=new StringBuffer();
  				sb1.append(key2);
  				sb2.append("'").append(val2).append("'");
  			}else{
  		         sb1.append(","+key2);
  				 sb2.append(",'"+val2).append("'");
  			}
  		
  		} 
         return String.format("insert into %s (%s) values(%s)", new Object[]{tableName,sb1.toString(),sb2.toString()});
  	}
  	
  	public static String getUpdateSQL(String tableName,HashMap<String,Object> pk,HashMap<String,Object> mapcol){
  		StringBuffer sb=null;
/*  		Iterator it=pk.entrySet().iterator();
  		while(it.hasNext()){
  			Map.Entry entry=(Map.Entry)it.next();
  			Object key = entry.getKey();
  			Object val = entry.getValue();
  			if(sb==null){
  				sb=new StringBuffer();
  				sb.append(key+"='"+val+"'");
  			}else{
  				sb.append(","+key+"='"+val+"'");
  			}
  		}*/
  		
  		
  		Iterator itcol=mapcol.entrySet().iterator();
  		while(itcol.hasNext()){
  			Map.Entry entry=(Map.Entry)itcol.next();
  			Object key = entry.getKey();
  			Object val = entry.getValue();
  			if(sb==null){
  				sb=new StringBuffer();
  				sb.append(key+"='"+val+"'");
  			}else{
  				sb.append(","+key+"='"+val+"'");
  			}
  		}
  		
  		System.out.println("getUpdateSQL:"+sb.toString());

  		return String.format("update %s set %s where %s ",new Object[]{tableName,sb.toString(),getIsExistWhere(pk)});
  	}
  	public static String getIsExistWhere(HashMap<String,Object> pk){
  		StringBuffer whereStr = null; 
  		Iterator it=pk.entrySet().iterator();
  		while(it.hasNext()){
  			Map.Entry entry=(Map.Entry)it.next();
  			Object key = entry.getKey();
  			Object val = entry.getValue();
  			if(whereStr==null){
  				whereStr=new StringBuffer();
  				whereStr.append(key+"='"+val+"'");
  			}else{
  				whereStr.append(" and "+key+"='"+val+"'");
  			}
  		}
  		return whereStr.toString();
  	}
    
  	public static String getlimitString(int page,int pagesize){
  		if(page<1){
  			page=1;
  		}
  		return " limit "+ (page - 1)*pagesize+ ","+ pagesize + "";
  	}
    
  	public static String getlimitString(int page){
  		int pagesize=10;
  		return getlimitString(pagesize); 
  	}
}  